<?php

/**
 * AccountDAO
 *
 */
class AccountDAO
{
    public function findAllByNameAndMac($name, $mac)
    {
        $db = Yii::app()->db;
        $active = Constants::STATUS_ACTIVE;

        $sql = 'SELECT m.member_id,m.name,m.nickname,m.password,m.mac_address,m.status AS member_status,m.parent_member_id, '
             . 'a.account_id, a.site_id, a.status, a.ad_status, a.grid_status, s.sitename '
             . 'FROM members AS m '
             . 'INNER JOIN accounts AS a ON m.member_id = a.member_id '
             . 'INNER JOIN sites AS s ON a.site_id = s.site_id '
             . 'WHERE m.status = :active '
             . 'AND m.nickname = :nickname '
             . 'AND m.mac_address = :mac '
             . 'ORDER BY a.site_id ASC';

        $command = $db->createCommand($sql);
        $command->bindParam(':active', $active);
        $command->bindParam(':nickname', $name);
        $command->bindParam(':mac', $mac);
        $result = $command->queryAll();

        if ($result) {
            return $result;
        }

        return NULL;
    }

    public function findStatus($name, $mac)
    {
        $db = Yii::app()->db;
        $status = Constants::ACCOUNT_ACTIVE;

        $sql = 'SELECT a.account_id AS accountId, a.name, a.site_id AS siteId, '
             . 'a.status AS loginStatus, a.ad_status AS adStatus, a.grid_status AS gridStatus, '
             . 's.has_captcha AS hasCaptcha, s.sitename AS siteName, s.site_url AS siteUrl, '
             . 's.login_url AS loginUrl, s.ad_url AS adUrl, '
             . 's.grid_url AS gridUrl, s.account_url AS accountUrl, s.logout_url AS logoutUrl, '
             . 's.captcha_login_url AS capLoginUrl, s.captcha_login_img_ex AS capLoginEx, '
             . 's.captcha_ad_url AS capAdUrl, s.captcha_ad_img_ex AS capAdEx, s.money_xpath AS moneyXpath, '
             . 's.login_success_xpath AS loginSuccessXpath, s.login_success_value AS loginSuccessValue '
             . 'FROM accounts AS a '
             . 'INNER JOIN members AS m ON a.member_id = m.member_id '
             . 'INNER JOIN sites AS s ON a.site_id = s.site_id '
             . 'WHERE m.nickname = :nickname AND m.mac_address = :mac '
             . 'AND s.status = :status AND m.status = :status '
             . 'AND a.status = :status '
             . 'ORDER BY account_id DESC ';

        $command = $db->createCommand($sql);
        $command->bindParam(':nickname', $name);
        $command->bindParam(':mac', $mac);
        $command->bindParam(':status', $status);
        $result = $command->queryAll();

        if ($result) {
            return $result;
        }

        return NULL;
    }

    public function findAccountByMacAndSiteId($mac, $siteId) {
        $db = Yii::app()->db;
        $deleteFlag = Constants::SITE_DELETE;

        $sql = 'SELECT a.account_id, a.name, a.site_id, a.status, a.ad_status, a.grid_status '
            . 'FROM accounts AS a '
            . 'WHERE a.site_id = :siteId AND a.mac_address = :mac '
            . 'AND a.status != :deleteStatus ';

        $command = $db->createCommand($sql);
        $command->bindParam(':siteId', $siteId);
        $command->bindParam(':mac', $mac);
        $command->bindParam(':deleteStatus', $deleteFlag);
        $result = $command->queryAll();

        if ($result) {
            return $result[0];
        }

        return NULL;
    }

    public function updateStatus($memberId, $data) {
        $db = Yii::app()->db;
        $deleteFlag = Constants::DELETE_FLAG_TRUE;

        $sql = 'UPDATE accounts SET '
             . 'status = :status, '
             . 'ad_status = :adStatus, '
             . 'grid_status = :gridStatus '
             . 'WHERE site_id = :siteId '
             . 'AND member_id = :memberId ';

        $command = $db->createCommand($sql);
        $command->bindParam(':status', $data['loginStatus']);
        $command->bindParam(':adStatus', $data['adStatus']);
        $command->bindParam(':gridStatus', $data['gridStatus']);
        $command->bindParam(':siteId', $data['siteId']);
        $command->bindParam(':memberId', $memberId);
        $command->execute();

        return TRUE;
    }
}